package com.yh.csx.bsf.ods.tidb;

import java.sql.Types;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import org.apache.commons.lang.StringEscapeUtils;
import org.apache.commons.lang3.StringUtils;
import com.yh.csx.bsf.ods.meta.MysqlDataType;

public class TidbTypeUtils {
	public static final String DATETIMEFORMAT="yyyy-MM-dd HH:mm:ss";
	public static final String DATEFORMAT="yyyy-MM-dd";
	public static final String TIMEFORMAT="HH:mm:ss";
	public static final Pattern DATE_PATTERN = Pattern.compile("^\\d{4}([-/.]?)\\d{2}([-/.]?)\\d{2}$");
	public static final Pattern TIME_PATTERN = Pattern.compile("^\\d{2}:\\d{2}:\\d{2}(\\.\\d+)?");
	public static final Pattern DATETIME_PATTERN = Pattern.compile("^\\d{4}([-/.]?)\\d{2}([-/.]?)\\d{2}\\s\\d{2}:\\d{2}:\\d{2}(\\.\\d+)?");

	public static int parseSqlType(String type) {

		MysqlDataType mysqlType = MysqlDataType.valueOf(type.toUpperCase());
		if (mysqlType == null)
			return Types.VARCHAR;
		switch (mysqlType) {
		case BIT:
			return Types.BIT;
		case TINYINT:
			return Types.TINYINT;
		case SMALLINT:
			return Types.SMALLINT;
		case INT:
			return Types.INTEGER;
		case BIGINT:
			return Types.BIGINT;
		case FLOAT:
			return Types.FLOAT;
		case REAL:
			return Types.REAL;
		case DOUBLE:
			return Types.DOUBLE;
		case NUMERIC:
			return Types.NUMERIC;
		case DECIMAL:
			return Types.DECIMAL;
		case CHAR:
			return Types.CHAR;
		case VARCHAR:
			return Types.VARCHAR;
		case LONGVARCHAR:
			return Types.LONGVARCHAR;
		case DATE:
			return Types.DATE;
		case TIME:
			return Types.TIME;
		case TIMESTAMP:
			return Types.TIMESTAMP;
		case BINARY:
			return Types.BINARY;
		case VARBINARY:
			return Types.VARBINARY;
		case LONGVARBINARY:
			return Types.LONGVARBINARY;
		case NULL:
			return Types.NULL;
		case JSON:
			return Types.VARCHAR;
		case OTHER:
			return Types.OTHER;
		case BLOB:
			return Types.BLOB;
		case REF:
			return Types.REF;
		case BOOLEAN:
			return Types.BOOLEAN;
		default:
			return Types.VARCHAR;
		}
	}

	/**
	 * 同步表结构用于解析默认值
	 */
	public static String perseDefaultValue(MysqlDataType type, String value) {
		if (value == null)
			return "null";
		switch (type) {
		case BIT:
		case TINYINT:
		case SMALLINT:
		case MEDIUMINT:
		case INT:
		case BIGINT:
		case FLOAT:
		case DOUBLE:
		case DECIMAL:
		case NUMERIC:
		case REAL:
		case YEAR:
			return value;
		case DATE:
			if (StringUtils.isBlank(value)) {
				return "null";
			}
			if (value.toString().contains("10000")) {
				value = value.toString().replace("10000", "9999");
			}
			Matcher m = DATE_PATTERN.matcher(value);
			if (m.find()) {
				return "'" + value + "'";
			}

			return value;
		case TIME:
			m = TIME_PATTERN.matcher(value);
			if (m.find()) {
				return "'" + value + "'";
			}
			return value;
		case DATETIME:
		case TIMESTAMP:
			if (StringUtils.isBlank(value)) {
				return "null";
			}
			String date = value;
			if (value.toString().contains("10000")) {
				date = value.toString().replace("10000", "9999");
			}
			m = DATETIME_PATTERN.matcher(date);
			if (m.find()) {
				return "'" + date + "'";
			}
			return date;
		case CHAR:
		case VARCHAR:
		case LONGVARCHAR:
		case TINYBLOB:
		case TINYTEXT:
		case BLOB:
		case TEXT:
		case MEDIUMTEXT:
		case MEDIUMBLOB:
		case LONGBLOB:
		case LONGTEXT:
		case ENUM:
		case SET:
		case BINARY:
		case VARBINARY:
		case LONGVARBINARY:
		case LINESTRING:
		case POINT:
		case MULTIPOINT:			
			
			return "'" + StringEscapeUtils.escapeSql(value.toString())  + "'";
		case JSON:
			if(StringUtils.isBlank(value)) {
				return "null";
			}
			return "'" +StringEscapeUtils.escapeSql(value.toString()) + "'";	
		case NULL:
			return "null";
		case OTHER:
		case REF:
		case BOOLEAN:
		case POLYGON:
		case MULTIPOLYGON:
		case GEOMETRY:
		case MULTILINESTRING:
		case GEOMETRYCOLLECTION:
			return "'" + value + "'";
		}
		return value;
	}

	public static String perseValue(MysqlDataType type, Object value) {
		if (value == null)
			return "null";
		switch (type) {
		case BIT:
		case TINYINT:
		case SMALLINT:
		case MEDIUMINT:
		case INT:
		case BIGINT:
		case FLOAT:
		case DOUBLE:
		case DECIMAL:
		case NUMERIC:
		case REAL:
		case YEAR:
			if (StringUtils.isBlank(value.toString()) || "null".equals(value.toString()))
				return "null";
			return value.toString();
		case DATE:
			if (StringUtils.isBlank(value.toString()) || "null".equals(value.toString()))
				return "null";
			return "'" + value.toString() + "'";
		case TIME:
			if (value == null || StringUtils.isBlank(value.toString()))
				return "null";
			if (value.toString().contains("10000-")) {
				value = value.toString().replace("10000-", "9999-");
			}
			if (value instanceof Date) {
				return "'" + (new SimpleDateFormat(TidbTypeUtils.TIMEFORMAT)).format(value) + "'";
			} else if (value instanceof String) {

				Date d = null;
				try {
					d = (new SimpleDateFormat(TidbTypeUtils.TIMEFORMAT)).parse(value.toString().replace("\\.*", ""));
				} catch (ParseException e) {
					e.printStackTrace();
				}
				if (d != null) {
					return "'" + (new SimpleDateFormat(TidbTypeUtils.TIMEFORMAT)).format(d) + "'";
				} else {
					return "null";
				}
			}
			return "'" + (new SimpleDateFormat(TidbTypeUtils.TIMEFORMAT)).format(value) + "'";
		case DATETIME:
			if (value == null || StringUtils.isBlank(value.toString()))
				return "null";
			String date = value.toString();
			if (date.contains("10000")) {
				date = date.replace("10000", "9999");
			}
			if (value instanceof Date) {
				date = (new SimpleDateFormat(TidbTypeUtils.DATETIMEFORMAT)).format(value);
				date = date.replace("10000-", "9999-");
				;
				return "'" + date + "'";
			} else if (value instanceof String) {

				Date d = null;
				try {
					d = (new SimpleDateFormat(TidbTypeUtils.DATETIMEFORMAT)).parse(date);
				} catch (ParseException e) {
					e.printStackTrace();
				}
				if (d != null) {
					return "'" + (new SimpleDateFormat(TidbTypeUtils.DATETIMEFORMAT)).format(d) + "'";
				} else {
					return "null";
				}
			}
			return "'" + (new SimpleDateFormat(TidbTypeUtils.DATETIMEFORMAT)).format(value) + "'";
		case TIMESTAMP:
			if (value == null || StringUtils.isBlank(value.toString()))
				return "null";
			date = value.toString();
			if (value.toString().contains("10000-")) {
				date = date.replace("10000-", "9999-");
			}
			if (value instanceof Date) {
				date = (new SimpleDateFormat(TidbTypeUtils.DATETIMEFORMAT)).format(value);
				date = date.replace("10000-", "9999-");
				;
				return "'" + date + "'";
			} else if (value instanceof String) {

				Date d = null;
				try {
					d = (new SimpleDateFormat(TidbTypeUtils.DATETIMEFORMAT)).parse(date.replace("\\.*", ""));
				} catch (ParseException e) {
					e.printStackTrace();
				}
				if (d != null) {
					return "'" + (new SimpleDateFormat(TidbTypeUtils.DATETIMEFORMAT)).format(d) + "'";
				} else {
					return "null";
				}
			}
			return "'" + (new SimpleDateFormat(TidbTypeUtils.DATETIMEFORMAT)).format(date) + "'";
		case CHAR:
		case VARCHAR:
		case LONGVARCHAR:
		case TINYBLOB:
		case TINYTEXT:
		case BLOB:
		case TEXT:
		case MEDIUMTEXT:
		case MEDIUMBLOB:
		case LONGBLOB:
		case LONGTEXT:
		case ENUM:
		case SET:
		case BINARY:
		case VARBINARY:
		case LONGVARBINARY:
		case LINESTRING:
		case POINT:
		case MULTIPOINT:
			return "'"+StringEscapeUtils.escapeSql(value.toString())+ "'";				
		case JSON:
			if(StringUtils.isBlank(value.toString()))
				return "'null'";
			return "'"+StringEscapeUtils.escapeSql(value.toString())+ "'";			
		case NULL:
			return "null";
		case OTHER:
		case REF:
		case BOOLEAN:
		case POLYGON:
		case MULTIPOLYGON:
		case GEOMETRY:
		case MULTILINESTRING:
		case GEOMETRYCOLLECTION:
			return "'" + value + "'";
		}
		return "'" + value.toString().replaceAll("\\'", "\\\\'") + "'";
	}

	public static Object perseObject(MysqlDataType type, Object value) throws ParseException {
		if (value == null)
			return null;
		switch (type) {
		case BIT:
		case TINYINT:
		case SMALLINT:
		case MEDIUMINT:
		case INT:
			if (StringUtils.isNotBlank(value.toString()))
				return Integer.valueOf(value.toString());
		case BIGINT:
			if (StringUtils.isNotBlank(value.toString()))
				return Long.valueOf(value.toString());
		case FLOAT:
			if (StringUtils.isNotBlank(value.toString()))
				return Float.valueOf(value.toString());
		case DOUBLE:
			if (StringUtils.isNotBlank(value.toString()))
				return Double.valueOf(value.toString());
		case DECIMAL:
		case NUMERIC:
		case REAL:
			if (StringUtils.isNotBlank(value.toString()))
				return Double.valueOf(value.toString());
		case YEAR:
			if (StringUtils.isNotBlank(value.toString()))
				return Integer.valueOf(value.toString());
			return null;
		case DATE:
		case TIME:
		case DATETIME:
		case TIMESTAMP:
			String date = value.toString();
			if (date.contains("10000")) {
				date = date.replace("10000", "9999");
			}
			if (StringUtils.isNotBlank(date))
				return date;
			return null;
		case CHAR:
		case VARCHAR:
		case LONGVARCHAR:
		case TINYBLOB:
		case TINYTEXT:
		case BLOB:
		case TEXT:
		case MEDIUMTEXT:
		case MEDIUMBLOB:
		case LONGBLOB:
		case LONGTEXT:
		case ENUM:
		case SET:
		case BINARY:
		case VARBINARY:
		case LONGVARBINARY:
		case LINESTRING:
		case POINT:
		case MULTIPOINT:
			return StringEscapeUtils.escapeSql(value.toString());
		case JSON:
			if(StringUtils.isBlank(value.toString())) {
				return null;
			}			
			return StringEscapeUtils.escapeSql(value.toString());	
		case NULL:
			return null;
		case OTHER:
		case REF:
		case BOOLEAN:
		case POLYGON:
		case MULTIPOLYGON:
		case GEOMETRY:
		case MULTILINESTRING:
		case GEOMETRYCOLLECTION:
			return value;
		}
		return  StringEscapeUtils.escapeSql(value.toString());

	}

	public static String perseValue(MysqlDataType type, Object value, String defaultValue) {
		if (value == null)
			return perseDefaultValue(type, defaultValue);
		switch (type) {
		case BIT:
		case TINYINT:
		case SMALLINT:
		case MEDIUMINT:
		case INT:
		case BIGINT:
		case FLOAT:
		case DOUBLE:
		case DECIMAL:
		case NUMERIC:
		case REAL:
		case YEAR:
			return value.toString();
		case DATE:
			return "'" + value.toString() + "'";
		case TIME:
			if (value == null || StringUtils.isBlank(value.toString()))
				return "";
			if (value.toString().contains("10000-")) {
				value = value.toString().replace("10000-", "9999-");
			}
			if (value instanceof Date) {
				return "'" + (new SimpleDateFormat("HH:mm:ss")).format(value) + "'";
			} else if (value instanceof String) {

				Date d = null;
				try {
					d = (new SimpleDateFormat("HH:mm:ss")).parse(value.toString().replace("\\.*", ""));
				} catch (ParseException e) {
					e.printStackTrace();
				}
				if (d != null) {
					return "'" + (new SimpleDateFormat("HH:mm:ss")).format(d) + "'";
				} else {
					return perseDefaultValue(type, defaultValue);
				}
			}
			return "'" + (new SimpleDateFormat("HH:mm:ss")).format(value) + "'";
		case DATETIME:
			if (value == null || StringUtils.isBlank(value.toString()))
				return perseDefaultValue(type, defaultValue);
			String date = value.toString();
			if (date.contains("10000")) {
				date = date.replace("10000", "9999");
			}
			if (value instanceof Date) {
				date = (new SimpleDateFormat(DATETIMEFORMAT)).format(value);
				date = date.replace("10000-", "9999-");
				;
				return "'" + date + "'";
			} else if (value instanceof String) {

				Date d = null;
				try {
					d = (new SimpleDateFormat(DATETIMEFORMAT)).parse(date.replace("\\.*", ""));
				} catch (ParseException e) {
					e.printStackTrace();
				}
				if (d != null) {
					return "'" + (new SimpleDateFormat(DATETIMEFORMAT)).format(d) + "'";
				} else {
					return perseDefaultValue(type, defaultValue);
				}
			}
			return "'" + (new SimpleDateFormat(DATETIMEFORMAT)).format(value) + "'";
		case TIMESTAMP:
			if (value == null || StringUtils.isBlank(value.toString()))
				return defaultValue;
			date = value.toString();
			if (value.toString().contains("10000-")) {
				date = date.replace("10000-", "9999-");
			}
			if (value instanceof Date) {
				date = (new SimpleDateFormat(DATETIMEFORMAT)).format(value);
				date = date.replace("10000-", "9999-");
				;
				return "'" + date + "'";
			} else if (value instanceof String) {

				Date d = null;
				try {
					d = (new SimpleDateFormat(DATETIMEFORMAT)).parse(date.replace("\\.*", ""));
				} catch (ParseException e) {
					e.printStackTrace();
				}
				if (d != null) {
					return "'" + (new SimpleDateFormat(DATETIMEFORMAT)).format(d) + "'";
				} else {
					return defaultValue;
				}
			}
			return "'" + (new SimpleDateFormat(DATETIMEFORMAT)).format(date) + "'";
		case CHAR:
		case VARCHAR:
		case LONGVARCHAR:
		case TINYBLOB:
		case TINYTEXT:
		case BLOB:
		case TEXT:
		case MEDIUMTEXT:
		case MEDIUMBLOB:
		case LONGBLOB:
		case LONGTEXT:
		case ENUM:
		case SET:
		case BINARY:
		case VARBINARY:
		case LONGVARBINARY:
		case LINESTRING:
		case POINT:
		case MULTIPOINT:
			return "'" + StringEscapeUtils.escapeSql(value.toString().replace("\\", ""))+ "'";
		case JSON:
			if (StringUtils.isNoneBlank(value.toString())) {
				return "'" +value.toString().replaceAll("NULL", "null").replaceAll("\\'", "\\\\'").replaceAll("\\\\\"", "\\\\\\\\\"")+ "'";
			}
			return "null";
		case NULL:
			return "null";
		case OTHER:
		case REF:
		case BOOLEAN:
		case POLYGON:
		case MULTIPOLYGON:
		case GEOMETRY:
		case MULTILINESTRING:
		case GEOMETRYCOLLECTION:
			return "'" + value + "'";
		}
		return "'" + StringEscapeUtils.escapeSql(value.toString().replaceAll("NULL", "null").replaceAll("\\\\\"", "\\\\\\\\\"").replaceAll("\'", "\\\\\'")) + "'";
	}
}
